import os
import pandas as pd
from sqlalchemy import create_engine
from pandasql import sqldf

pysqldf = lambda sql: sqldf(sql, globals())

path = os.getcwd()
engine = create_engine(
    'mysql+pymysql://lianuser:@lianuser123@rr-uf6801jkrnr28mw7f5m.mysql.rds.aliyuncs.com:25686/lm_yisdk_user')

engine_mysql8 = create_engine(
    'mysql+pymysql://root:@123456@localhost:3306/world')

engine_test = create_engine('mysql+pymysql://root:123456@localhost:3306/world?charset=utf8')

# 查询语句，选出employee表中的所有数据
sql = """
WITH temp_role_pay AS ( 
SELECT role_id, 
sum(charge_money)/100 AS sum_amount 
FROM lm_yisdk_user.yisdk_role 
WHERE game_id = '66' 
AND charge_money > 0 
GROUP BY role_id) 
SELECT *,CASE
	WHEN sum_amount < 100 AND sum_amount > 0 THEN '微R' 
	WHEN sum_amount >= 100 AND sum_amount < 500 THEN '小R'
	WHEN sum_amount >= 500 AND sum_amount < 1000 THEN '中R★' 
	WHEN sum_amount >= 1000 AND sum_amount < 1500 THEN '中R★★' 
	WHEN sum_amount >= 1500 AND sum_amount < 2000 THEN '中R★★★' 
	WHEN sum_amount >= 2000 AND sum_amount < 2500 THEN '中R★★★★' 
	WHEN sum_amount >= 2500 AND sum_amount < 3000 THEN '中R★★★★★' 
	WHEN sum_amount >= 3000 AND sum_amount < 5000 THEN '大R★' 
	WHEN sum_amount >= 5000 AND sum_amount < 7000 THEN '大R★★' 
	WHEN sum_amount >= 7000 AND sum_amount < 8000 THEN '大R★★★' 
	WHEN sum_amount >= 8000 AND sum_amount < 9000 THEN '大R★★★★' 
	WHEN sum_amount >= 9000 AND sum_amount < 10000 THEN '大R★★★★★' 
	WHEN sum_amount >= 10000 AND sum_amount < 20000 THEN '超R★' 
	WHEN sum_amount >= 20000 AND sum_amount < 30000 THEN '超R★★' 
	WHEN sum_amount >= 30000 AND sum_amount < 40000 THEN '超R★★★' 
	WHEN sum_amount >= 40000 AND sum_amount < 50000 THEN '超R★★★★' 
	WHEN sum_amount >= 50000 THEN '超R★★★★★' ELSE '未充值' END AS vip_type 
	FROM temp_role_pay;"""

# read_sql_query的两个参数: sql语句， 数据库连接
df_vip_info = pd.read_sql_query(sql, engine)  # 计算角色充值分层数据

dt_role_qq = pd.read_excel(path + r"\QQ总添加名单.xlsx",
                           converters={"QQ号": str, "角色ID": str, "手机号": str, "区服": str})

sql_vip_qq = """
select a.*,b.qq号 as qq from df_vip_info as a
left join dt_role_qq as b
on a.role_id = b.角色ID
"""

df_vip_qq = pysqldf(sql_vip_qq)

# 计算VIP分层充值与人数
sql_vip_result = """
SELECT vip_type,count(1) rolecnt,
sum(sum_amount) sum_amount,
count(case when `qq` is NULL THEN NULL else role_id END) as qq_role_cnt,
sum(case when `qq` is NULL THEN 0 else sum_amount END) as qq_amount
FROM df_vip_qq
GROUP BY vip_type
"""

df_vip_result = pysqldf(sql_vip_result)
df_vip_result.to_excel('df_vip_result.xlsx', index=False)
print("程序运行结束")
